package com.ysd.lis.mapper.samsto;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ysd.lis.dto.samsto.SamstoStorageFridgeDto;
import com.ysd.lis.entity.samsto.SamstoStorageFridge;
import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * (SamstoStorageFridge)表数据库访问层
 *
 * @author leiming
 * @since 2024-10-25 14:34:48
 */
public interface SamstoStorageFridgeMapper extends MPJBaseMapper<SamstoStorageFridge> {

    @Select("SELECT a.STORAGE_ID AS storageId," +
            "COUNT(DISTINCT(t.ID)) AS  sfNum," +
            "COUNT(DISTINCT(b.ID)) AS  sflNum," +
            "COUNT(DISTINCT(c.ID)) AS  ssNum," +
            "COUNT(DISTINCT(d.ID)) AS  ssoNum," +
            "SUM(d.EMPTY) AS  ssoNotEemptyNum " +
            "FROM SAMSTO_STORAGE_FRIDGE a " +
            "LEFT JOIN	SAMSTO_FRIDGE t ON t.id=a.FRIDGE_ID " +
            "LEFT JOIN SAMSTO_FRIDGE_LAYER b ON t.ID = b.FRIDGE_ID " +
            "LEFT JOIN SAMSTO_SPECBOX c ON b.ID = c.LAYER_ID " +
            "LEFT JOIN SAMSTO_SPECBOX_ORDER d ON c.ID = d.SPECBOX_ID GROUP BY a.STORAGE_ID")
    List<SamstoStorageFridgeDto> getSamstoStorageData();
    @Select("SELECT t.ID AS fridgeId," +
            "COUNT(DISTINCT(b.ID)) AS  sflNum, " +
            "COUNT(DISTINCT(c.ID)) AS  ssNum, " +
            "COUNT(DISTINCT(d.ID)) AS  ssoNum, " +
            "SUM(d.EMPTY) AS  ssoNotEemptyNum  " +
            "FROM SAMSTO_FRIDGE t  " +
            "LEFT JOIN SAMSTO_FRIDGE_LAYER b ON t.ID = b.FRIDGE_ID  " +
            "LEFT JOIN SAMSTO_SPECBOX c ON b.ID = c.LAYER_ID  " +
            "LEFT JOIN SAMSTO_SPECBOX_ORDER d ON c.ID = d.SPECBOX_ID " +
            "GROUP BY t.ID")
    List<SamstoStorageFridgeDto> getFridgeData();
}

